<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"
    import="java.sql.*"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>預借服務</title>
<script language="javascript">
var pre_url = "classificationquery.jsp";
function select1_changed()
{
	this_select = document.form1.select1.selectedIndex;
	document.getElementById(0).style.display="none";
	document.getElementById(1).style.display="none";
	document.getElementById(2).style.display="none";
	document.getElementById('sel2').value="";
	document.getElementById('sel3').value="";
	if(this_select != 0)
	{
		document.getElementById(this_select).style.display="block";
		document.getElementById(0).style.display="block";
	}
	else
	{
		this_select = 0;
		sub_select = 0;
		document.form2.action= pre_url;
		document.form1.select1.selectedIndex=0;
		document.getElementById("submit5").style.display="none";
	}
}

function select2_changed()
{
	var box = document.form2.select2;
	sub_select = box.selectedIndex;
	document.getElementById('sel3').value="";
	if(sub_select !=0){
		document.getElementById("submit5").style.display="block";
	}else{
	document.getElementById("submit5").style.display="none";
	}
}
function select3_changed()
{
	var box = document.form2.select3;
	sub_select = box.selectedIndex;
	document.getElementById('sel2').value="";
	if(sub_select !=0){
		document.getElementById("submit5").style.display="block";
	}else{
	document.getElementById("submit5").style.display="none";
	}	
}
</script>
<style type="text/css">
<!--
.style3 {
	font-size: 12px;
	font-weight: bold;
	color: #FF0000;
}
-->
</style>
</head>
 
<body>
<% if(session.getAttribute("access") == "y" ){%>
<jsp:useBean id="mydb" scope="page" class="bean.ConDbBean"/> <%//連結資料庫的bean %>
<jsp:useBean id="p_state" scope="page" class="bean.StateBean"/>
<jsp:useBean id="pclass" scope="page" class="myclass.P_Class"/>
<table width="750" border="0" align="center">
  <tr>
    <td width="30" rowspan="4"><jsp:include page="../frameset/left.jsp" flush="true" ></jsp:include></td>
    <td colspan="2"><jsp:include page="../frameset/top.jsp" flush="true" ></jsp:include></td>
    <td width="40" rowspan="4"><jsp:include page="../frameset/right.jsp" flush="true" ></jsp:include></td>
  </tr>
  <tr>
    <td rowspan="2"><jsp:include page="../frameset/mean.jsp" flush="true" ></jsp:include>

    </td>
    <td height="32"><jsp:include page="../frameset/book_mean.jsp" flush="true" ></jsp:include></td>
  </tr>
  <tr>
    <td>
    <%--內頁--%>
    <%
    String date=""; %>
 <table width="550" border="0" align="center">
  <tr>
    <td width="54%" rowspan="3">
    <jsp:include page="showCal.jsp" flush="true" ></jsp:include>
    </td>
    <td width="18%">預借日期</td>
    <td width="28%">
    <%
    if(request.getParameter("mon")==null){%>
    <span class="style3">*請點選旁邊的行事曆</span>
    <%
    }
     else{
     	date=request.getParameter("year")+"/"+request.getParameter("mon")+"/"+request.getParameter("day");
    %>
    <input name="time" type="text" id="datee" value="<%=date%>">
    <%
     }%>
    </td>
  </tr>
  <%
    if(request.getParameter("mon")!=null){
    %>
    <tr>
    <td>借用類別</td>
    <td>
    <div style="float:left;">
  		<form name="form1" method="post" action="lookBook.jsp">
    		<select name="select1" id="select1" onchange="select1_changed()">
	  		<option value="">請選擇</option>
      		<option value="place">場地</option>
      		<option value="facility">設備</option>
    		</select>
  		</form>
  	</div>
    </td>
  </tr>
    <%
    }
    else{%>
     <tr><td></td>
    	<td></td></tr>
    <%
    }%>
  
  <tr>
    <td><div id="0" style="display:none; float:left;">預借項目</div></td>
    <td>
    	<form name="form2" method="post" action="lookBook.jsp">
<%
				String sql="select * from PLACE_CLASS ";
	  			ResultSet rs=mydb.executeQuery(sql);
	  			sql="select * from FACILITY_CLASS ";
	  			ResultSet rs2=mydb.executeQuery(sql);
 %>
				<div id="1" style="display:none; float:left;">
				<select name="select2" id="sel2" onchange="select2_changed()">
				<option value="">請選擇</option>
    			<% while(rs.next()){%>
    				<option value="<%=rs.getString("PC_ID")%>"><%=rs.getString("PC_NAME")%></option>
    			<%
    			}
    			%>
  				</select>
				</div>
				<div id="2" style="display:none; float:left;">
				<select name="select3" id="sel3" onchange="select3_changed()">
				<option value="">請選擇</option>
    			<% while(rs2.next()){%>
    				<option value="<%=rs2.getString("FC_ID")%>"><%=rs2.getString("FC_NAME")%></option>
    			<%
    			}
    			%>
  				</select>
  				</div>
  				
  				<input type="hidden" name="b_date" id="b_date" value="<%=date%>">
  				<div id="submit5" style="float:left; display:none">
				<input type="submit" name="Submit2" value="送出"/>
				</div>
			</form>
    </td>
  </tr>
  <tr>
    <td colspan="3">
    <%//秀出我所要借用的資料(開始) %>
    
    <%
	if((request.getParameter("select2")==null) && (request.getParameter("select3")==null)&& (request.getParameter("select4")==null)){
	}
	else{
		int nTotal;
		ResultSet rs4;
		ResultSet rs3;
		ResultSet rs5;
		ResultSet rs6;
		ResultSet rs7;
		String sql2;
		if(request.getParameter("select2")!=""){ //選擇場地
			String pc_id=request.getParameter("select2");
			sql="select * from PLACE WHERE P_CLASS='"+pc_id+"'";
			rs3=mydb.executeQuery(sql);
			sql="select COUNT(P_ID) from PLACE WHERE P_CLASS='"+pc_id+"'"; //算total筆數
	  		rs4=mydb.executeQuery(sql); 
	  		rs4.next();
	  		nTotal=java.lang.Integer.valueOf(rs4.getString(""));
	  		if(nTotal==0){%>
	  			<table width="500" border="1" align="center">
  					<tr>
    					<td>搜尋不到任何相關資料</td>
  					</tr>
				</table>
				<%
			}
			else{
			%>
	  		<table width="500" border="1" align="center">
  				<tr>
      					<td colspan="7" align="center" bgcolor="#FFFF66"><%=request.getParameter("b_date") %></td>
    			</tr>
  				<tr>
    				<td>場地編號</td>
    				<td>地點名稱</td>
    				<td>上午</td>
    				<td>中午</td>
    				<td>下午</td>
    				<td>晚上</td>
    				<td>預借</td>
  				</tr>
  			<%
  				while(rs3.next()){
  					char[]ch;
  					String strCh="";
  					int now_state=0;
  					int now_state_a=0;
  					int now_state_b=0;
  					int now_state_c=0;
  					int now_state_d=0;
  			 %>
  			<form id="book" name="form1" method="post" action="add_Pbook.jsp">
  				<tr>
    				<td><a href="../PFManage/detailPlace.jsp?id=<%=rs3.getString("P_ID")%>" target="_black" ><%=rs3.getString("P_ID")%></a><input type="hidden" name="p_id" value="<%=rs3.getString("P_ID")%>"></td>
    				<td><%=rs3.getString("P_NAME")%><input type="hidden" name="p_name" value="<%=rs3.getString("P_NAME")%>"></td>
  				<%//讀借用或預借的資料庫,看看此筆資料是不是已經被借用或預借,若有則不讓此場地設備再被預借!
    						sql="select * from BOOK WHERE PF_ID='"+rs3.getString("P_ID")+"' and APPLY_DATE='"+request.getParameter("b_date")+"'";
							rs6=mydb.executeQuery(sql);
							if(rs6.next()){//有預借的資料
								do{
									now_state=1;
									ch = rs6.getString("BOOK_TIME").toCharArray();
									for(int i=0;i<ch.length;i++){
										strCh="";
										strCh+=ch[i];
										if(strCh.equalsIgnoreCase("a")){
											now_state_a=1;
										}else if(strCh.equalsIgnoreCase("b")){
											now_state_b=1;
										}else if(strCh.equalsIgnoreCase("c")){
											now_state_c=1;
										}else if(strCh.equalsIgnoreCase("d")){
											now_state_d=1;
										}
									}
								}while(rs6.next());
							}else{
								now_state=0;
							}
				
							//檢查借用資料
							sql="select * from BORROW WHERE PF_ID='"+rs3.getString("P_ID")+"'and MUST_RETURN_DATE<'"+request.getParameter("b_date")+"'and RETURN_DATE is null";
							rs6=mydb.executeQuery(sql);
							if(rs6.next()){//假如此筆借用資料已經逾期,則不再讓人借用
								now_state_a=2;
								now_state_b=2;
								now_state_c=2;
								now_state_d=2;
							}else{
								sql="select * from BORROW WHERE PF_ID='"+rs3.getString("P_ID")+"'and BORROW_DATE='"+request.getParameter("b_date")+"'";
								rs6=mydb.executeQuery(sql);
								if(rs6.next()){//有借用的資料
									do{
										now_state=2;
										ch = rs6.getString("BORROW_TIME").toCharArray();
										for(int i=0;i<ch.length;i++){
											strCh="";
											strCh+=ch[i];
											if(strCh.equalsIgnoreCase("a")){
												now_state_a=2;
											}else if(strCh.equalsIgnoreCase("b")){
												now_state_b=2;
											}else if(strCh.equalsIgnoreCase("c")){
												now_state_c=2;
											}else if(strCh.equalsIgnoreCase("d")){
												now_state_d=2;
											}
										}
									}while(rs6.next());
								}else{
									now_state=0;
								}
							}
    					 %>
    					<td>
    					<%if(now_state_a==0) {
    					%>
    					<input type="checkbox" name="book_time" value="a" />
    					<%
    					}else if(now_state_a==1){%>
    						<img src="images/book.jpg"/><%
    					}else if(now_state_a==2){%>
    						<img src="images/borrow.jpg"/><%
    					}
    					%>
    					</td>
    					<td>
    					<%if(now_state_b==0) {
    					%>
    					<input type="checkbox" name="book_time" value="b" />
    					<%
    					}else if(now_state_b==1){%>
    						<img src="images/book.jpg"/><%
    					}else if(now_state_b==2){%>
    						<img src="images/borrow.jpg"/><%
    					}
    					%>
    					</td>
    					<td>
    					<%if(now_state_c==0) {
    					%>
    					<input type="checkbox" name="book_time" value="c" />
    					<%
    					}else if(now_state_c==1){%>
    						<img src="images/book.jpg"/><%
    					}else if(now_state_c==2){%>
    						<img src="images/borrow.jpg"/><%
    					}
    					%>
    					</td>
    					<td>
    					<%if(now_state_d==0) {
    					%>
    					<input type="checkbox" name="book_time" value="d" />
    					<%
    					}else if(now_state_d==1){%>
    						<img src="images/book.jpg"/><%
    					}else if(now_state_d==2){%>
    						<img src="images/borrow.jpg"/><%
    					}
    					%>
    					</td>
    					
    					<td>
    					<%
    					if(now_state_a!=0 && now_state_b!=0 && now_state_c!=0 && now_state_d!=0){
    						out.print("無法預借");
    					}else{%>
    						<input type="submit" name="Submit" value="預借" />
    					<%
    					}
    					 %>
    					</td>
    					<input type="hidden" name="book_date" value="<%=request.getParameter("b_date")%>"/>
  					</tr></form>
  			<%		} %>
				</table>
	  	<%
		
			}
		}
		if(request.getParameter("select3")!=""){//選擇設備
			String fc_id=request.getParameter("select3");
			sql="select * from FACILITY WHERE F_CLASS='"+fc_id+"'";
			rs3=mydb.executeQuery(sql);
			sql="select COUNT(F_ID) from FACILITY WHERE F_CLASS='"+fc_id+"'"; //算total筆數
	  		rs4=mydb.executeQuery(sql); 
	  		rs4.next();
	  		nTotal=java.lang.Integer.valueOf(rs4.getString(""));
	  		if(nTotal==0){%>
	  			<table width="500" border="1" align="center">
  					<tr>
    					<td>搜尋不到任何相關資料</td>
  					</tr>
				</table>
		<%	}
			else{
		%>
	  			<table width="500" border="1" align="center">
  					<tr>
      					<td colspan="7" align="center" bgcolor="#FFFF66"><%=request.getParameter("b_date") %></td>
    				</tr>
  					<tr>
    					<td>設備編號</td>
    					<td>設備名稱</td>
    					<td>上午</td>
    					<td>中午</td>
    					<td>下午</td>
    					<td>晚上</td>
    					<td>預借</td>
  					</tr>
  			<%
  					
  					//int Tn=0;
  					
  					
  					while(rs3.next()){
  						char[]ch;
  						String strCh="";
  						int now_state=0;
  						int now_state_a=0;
  						int now_state_b=0;
  						int now_state_c=0;
  						int now_state_d=0;
  			 %>
  			 <form id="book" name="form1" method="post" action="add_book.jsp">
  					<tr>
    					<td><a href="../PFManage/detailFacility.jsp?id=<%=rs3.getString("F_ID")%>" target="_black"><%=rs3.getString("F_ID")%></a> <input type="hidden" name="f_id" id="f_id" value="<%=rs3.getString("F_ID")%>"></td>
    					<td><%=rs3.getString("F_NAME")%><input type="hidden" name="f_name" id="f_name" value="<%=rs3.getString("F_NAME")%>"></td>
    					<%//讀借用或預借的資料庫,看看此筆資料是不是已經被借用或預借,若有則不讓此場地設備再被預借!
    						sql="select * from BOOK WHERE PF_ID='"+rs3.getString("F_ID")+"' and APPLY_DATE='"+request.getParameter("b_date")+"'";
							rs6=mydb.executeQuery(sql);
							if(rs6.next()){
								do{
									now_state=1;
									ch = rs6.getString("BOOK_TIME").toCharArray();
									for(int i=0;i<ch.length;i++){
										strCh="";
										strCh+=ch[i];
										if(strCh.equalsIgnoreCase("a")){
											now_state_a=1;
										}else if(strCh.equalsIgnoreCase("b")){
											now_state_b=1;
										}else if(strCh.equalsIgnoreCase("c")){
											now_state_c=1;
										}else if(strCh.equalsIgnoreCase("d")){
											now_state_d=1;
										}
									}
								}while(rs6.next());
							}else{
								now_state=0;
							}
							//檢查借用資料
							sql="select * from BORROW WHERE PF_ID='"+rs3.getString("F_ID")+"'and MUST_RETURN_DATE<'"+request.getParameter("b_date")+"'and RETURN_DATE is null";
							rs6=mydb.executeQuery(sql);
							if(rs6.next()){//假如此筆借用資料已經逾期,則不再讓人借用
								now_state_a=2;
								now_state_b=2;
								now_state_c=2;
								now_state_d=2;
							}else{
								sql="select * from BORROW WHERE PF_ID='"+rs3.getString("F_ID")+"'and BORROW_DATE='"+request.getParameter("b_date")+"'";
								rs6=mydb.executeQuery(sql);
								if(rs6.next()){
									do{
										now_state=2;
										ch = rs6.getString("BORROW_TIME").toCharArray();
										for(int i=0;i<ch.length;i++){
											strCh="";
											strCh+=ch[i];
											if(strCh.equalsIgnoreCase("a")){
												now_state_a=2;
											}else if(strCh.equalsIgnoreCase("b")){
												now_state_b=2;
											}else if(strCh.equalsIgnoreCase("c")){
												now_state_c=2;
											}else if(strCh.equalsIgnoreCase("d")){
												now_state_d=2;
											}
										}
									}while(rs6.next());
								}else{
									now_state=0;
								}
							}
							
    					 %>
    					<td>
    					<%if(now_state_a==0) {
    					%>
    					<input type="checkbox" name="book_time" value="a" />
    					<%
    					}else if(now_state_a==1){%>
    						<img src="images/book.jpg"/><%
    					}else if(now_state_a==2){%>
    						<img src="images/borrow.jpg"/><%
    					}
    					%>
    					</td>
    					<td>
    					<%if(now_state_b==0) {
    					%>
    					<input type="checkbox" name="book_time" value="b" />
    					<%
    					}else if(now_state_b==1){%>
    						<img src="images/book.jpg"/><%
    					}else if(now_state_b==2){%>
    						<img src="images/borrow.jpg"/><%
    					}
    					%>
    					</td>
    					<td>
    					<%if(now_state_c==0) {
    					%>
    					<input type="checkbox" name="book_time" value="c" />
    					<%
    					}else if(now_state_c==1){%>
    						<img src="images/book.jpg"/><%
    					}else if(now_state_c==2){%>
    						<img src="images/borrow.jpg"/><%
    					}
    					%>
    					</td>
    					<td>
    					<%if(now_state_d==0) {
    					%>
    					<input type="checkbox" name="book_time" value="d" />
    					<%
    					}else if(now_state_d==1){%>
    						<img src="images/book.jpg"/><%
    					}else if(now_state_d==2){%>
    						<img src="images/borrow.jpg"/><%
    					}
    					%>
    					</td>
    					
    					<td>
    					<%
    					if(now_state_a!=0 && now_state_b!=0 && now_state_c!=0 && now_state_d!=0){
    						out.print("無法預借");
    					}else{%>
    						<input type="submit" name="Submit" value="預借" />
    					<%
    					}
    					 %>
    					</td>
    					<input type="hidden" name="book_date" value="<%=request.getParameter("b_date")%>"/>
  					</tr></form>
  			<%		} %>
				</table>
	  	<%
			}
	
		}
	}
 %>
    <%//結束 %>
    </td>
  </tr>
</table>   
    
    
    
<%--內頁end--%>
    </td>
  </tr>
  <tr>
    <td colspan="2"></td>
  </tr>
</table>
<%
  }
  else{
  	response.sendRedirect("../index.jsp"); //若沒有登入則回到index.jsp
  } 
  %>
</body>
</html>
